package dhu.cst.jnlwt.jiangnan181310209liwanting181310302.tally.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Locale;

import dhu.cst.jnlwt.jiangnan181310209liwanting181310302.tally.entity.AccountBean;
import dhu.cst.jnlwt.jiangnan181310209liwanting181310302.tally.entity.CategoryBean;

public class AccountDao {
    private DatabaseHelper helper;
    private SQLiteDatabase db;
    private int userId;
    public AccountDao(Context context, String phoneNumber) {
        helper = new DatabaseHelper(context);
        db = helper.getReadableDatabase();
        //通过手机号获得用户id，获得用户的收入支出表
        Cursor cursor = db.query("users",new String[]{"_id"},"phone=?",new String[]{phoneNumber},
                null,null,null);
        cursor.moveToFirst();
        userId = cursor.getInt(cursor.getColumnIndex("_id"));
        cursor.close();
    }
    public AccountDao(Context context) {
        helper = new DatabaseHelper(context);
        db = helper.getReadableDatabase();
    }
    //获取belong所有类别
    public List<CategoryBean> getCategories(String belong_input){
        ArrayList<CategoryBean> result = new ArrayList<CategoryBean>();
        String sql = "SELECT _id,category,type,belong,icon FROM categories WHERE belong='"+belong_input+"'";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            int id = cursor.getInt(cursor.getColumnIndex("_id"));
            String category = cursor.getString(cursor.getColumnIndex("category"));
            int type = cursor.getInt(cursor.getColumnIndex("type"));
            String belong = cursor.getString(cursor.getColumnIndex("belong"));
            int icon = cursor.getInt(cursor.getColumnIndex("icon"));

            CategoryBean categoryItem = new CategoryBean(id,category,type,belong,icon);
            result.add(categoryItem);
        }
        cursor.close();
        return result;
    }
    //获取该用户收入明细列表
    public List<AccountBean> getIncomeList(String date){
        ArrayList<AccountBean> result = new ArrayList<AccountBean>();
        String sql = "SELECT _id,account,category,date,postscript FROM income"+userId+" WHERE date LIKE '"+date+"%'";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            AccountBean item = new AccountBean();
            item.setId(cursor.getInt(cursor.getColumnIndex("_id")));
            item.setAccount(cursor.getDouble(cursor.getColumnIndex("account")));
            item.setCategory(cursor.getString(cursor.getColumnIndex("category")));
            item.setDate(cursor.getString(cursor.getColumnIndex("date")));
            item.setPostscript(cursor.getString(cursor.getColumnIndex("postscript")));
            result.add(item);
        }
        cursor.close();
        return result;
    }
    //获取该用户支出明细列表
    public List<AccountBean> getExpenditureList(String date){
        ArrayList<AccountBean> result = new ArrayList<AccountBean>();
        String sql = "SELECT _id,account,category,date,postscript FROM expenditure"+userId+" WHERE date LIKE '"+date+"%'";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            AccountBean item = new AccountBean();
            item.setId(cursor.getInt(cursor.getColumnIndex("_id")));
            item.setAccount(cursor.getDouble(cursor.getColumnIndex("account")));
            item.setCategory(cursor.getString(cursor.getColumnIndex("category")));
            item.setDate(cursor.getString(cursor.getColumnIndex("date")));
            item.setPostscript(cursor.getString(cursor.getColumnIndex("postscript")));
            result.add(item);
        }
        cursor.close();
        return result;
    }
    //添加一条收入明细
    public void addIncomeItem(AccountBean item) {

        ContentValues values = new ContentValues();
        values.put("account",item.getAccount());
        values.put("category",item.getCategory());
        values.put("date",item.getDate());
        values.put("postscript",item.getPostscript());
        String name = "income"+userId;
        db.insert(name,null,values);
    }
    //添加一条支出明细
    public void addExpenditureItem(AccountBean item) {

        ContentValues values = new ContentValues();
        values.put("account",item.getAccount());
        values.put("category",item.getCategory());
        values.put("date",item.getDate());
        values.put("postscript",item.getPostscript());
        db.insert("expenditure"+userId,null,values);
    }
    /*获取date的收入分类汇总*/
    public List<AccountBean> getIncomeStatisticsList(String date){
        ArrayList<AccountBean> result = new ArrayList<AccountBean>();
        String sql = "SELECT category,SUM(account) AS account FROM income"+userId+" WHERE date LIKE '"+date+"%' GROUP BY category";
        Cursor cursor = db.rawQuery(sql,null);
        while(cursor.moveToNext()){
            AccountBean item = new AccountBean();
            item.setCategory(cursor.getString(cursor.getColumnIndex("category")));
            item.setAccount(cursor.getDouble(cursor.getColumnIndex("account")));
            result.add(item);
        }
        cursor.close();
        return result;
    }
    /*获取date的支出分类汇总*/
    public List<AccountBean> getExpenditureStatisticsList(String date){
        ArrayList<AccountBean> result = new ArrayList<AccountBean>();
        String sql = "SELECT belong,SUM(account) account FROM expenditure"+userId+",categories WHERE expenditure"+userId+".category=categories.category AND date LIKE '"+date+"%' GROUP BY belong";
        Cursor cursor = db.rawQuery(sql,null);
        while(cursor.moveToNext()){
            AccountBean item = new AccountBean();
            item.setCategory(cursor.getString(cursor.getColumnIndex("belong")));
            item.setAccount(cursor.getDouble(cursor.getColumnIndex("account")));
            result.add(item);
        }
        cursor.close();
        return result;
    }
    //获取category的图标
    public int getIcon(String category){
        Cursor cursor = db.query("categories",new String[]{"icon"},"category=?",new String[]{category},null,null,null);
        cursor.moveToNext();
        int icon = cursor.getInt(cursor.getColumnIndex("icon"));
        cursor.close();
        return icon;
    }
    //获取category是支出还是收入
    public int getCategoryType(String category){
        Cursor cursor = db.query("categories",new String[]{"type"},"category=?",new String[]{category},null,null,null);
        cursor.moveToNext();
        int type = cursor.getInt(cursor.getColumnIndex("type"));
        cursor.close();
        return type;
    }
}
